# encoding: utf-8
__author__ = 'wwj'
import re
import sys
reload(sys)
sys.setdefaultencoding('utf8')
sys.path.append("/Users/wwj/Downloads/shangtianjun/girl/girl/dbShell/")
sys.path.append( "/var/crawler/spider/crawler/girl/dbShell/")
from dbshell import dbshell
from zlib import crc32
from collections import defaultdict,OrderedDict
from datetime import *
from pprint import pprint as pp
import MySQLdb
import MySQLdb.cursors
import copy
# from ..dbshell import dbshell

class main(dbshell):
    orderItem = [
            # ['url', ''],
            ['gid', ''],
            ['sid', ''],
            ['time', ''],
            ['date', ''],

    ]
    existgirl = {}
    mongoTable = "attendance"
    mysqlTable = "agenda"
    exesize = 200
    def __init__(self,spidername):
        super(main,self).__init__(spidername)
        # self.geturltool("select distinct(url) from store")
        today = str(date.today())
        self.mongofrom = self.mongo.find({"attendance":{"$exists":True},"date":today},no_cursor_timeout=True).batch_size(self.exesize)
        self.getdata()

    def getdata(self):
        if not self.redisconn.exists("girls"):
            sql = "select distinct(url),id from girl"
            tmp = self.getdb(sql)
            for x in tmp:
                self.redisconn.hset("girls",x["url"],x["id"])
        if not self.redisconn.exists("stores"):
            tmp = self.getdb("select distinct(url),id from store")
            for x in tmp:
                self.redisconn.hset("stores",x["url"],x["id"])

    def clean(self,y):
        data = OrderedDict(self.orderItem)
        tmp = re.search("(\d+-\d+-\d+)",y["date"])
        if tmp:
            tmp = tmp.group()
        if datetime.strptime(tmp,"%Y-%m-%d") + timedelta(days=3) < datetime.today():
            # import ipdb;ipdb.set_trace()
            return "time"
        storeUrl = y["url"].replace("https://www.cityheaven.net","")
        if isinstance(y.get("attendance"),dict):
            for x,xx in y.get("attendance").iteritems():
                sid = self.redisconn.hget("stores",storeUrl)
                if not sid:
                    continue
                data["sid"] = sid
                gid = self.redisconn.hget("girls",x)
                if not gid:
                    continue
                data["gid"] = gid
                i = 0
                for xxx in xx:
                    # import ipdb;ipdb.set_trace()
                    datestr = datetime.strptime(tmp,"%Y-%m-%d") + timedelta(days=i)
                    data["date"] = datestr
                    data["time"] = xxx.strip()
                    i += 1
                    # import ipdb;ipdb.set_trace()
                    tmpdata = copy.deepcopy(data)
                    self.datas.append(tmpdata)
                    if datetime.strptime(tmp,"%Y-%m-%d") + timedelta(days=i) < datetime.today():
                        continue

        else:
            i = 0
            for x in y.get("attendance"):
                # import ipdb;ipdb.set_trace()
                # tmp = re.search("(\d+-\d+-\d+)",y["date"])
                # if tmp:
                #     tmp = tmp.group()
                for xx in x:
                    datestr = datetime.strptime(tmp,"%Y-%m-%d") + timedelta(days=i)
                    data["date"] = datestr
                    sid = self.redisconn.hget("stores",storeUrl)
                    if not sid:
                        continue
                    data["sid"] = sid
                    gid = self.redisconn.hget("girls",xx[0].replace("http://www.dmm.co.jp",""))
                    if not gid:
                        continue
                    data["gid"] = gid
                    data["time"] = xx[1].strip()
                    tmpdata = copy.deepcopy(data)
                    self.datas.append(tmpdata)
                i += 1
                if datetime.strptime(tmp,"%Y-%m-%d") + timedelta(days=i) < datetime.today():
                    continue
        # import ipdb;ipdb.set_trace()
        # return data

    def buildsql(self,data):
        keys = self.data.keys()
        dd = []
        data_values = "(" + "%s," * (len(keys)) + ")"
        data_values = data_values.replace(',)', ')')
        keys = str(tuple(keys)).replace("'",'')
        values = [data]
        for value in values:
            dd = []
            sql = "replace into %s %s values %s" % (self.mysqlTable,keys,data_values)
            for v in value:
                dd.append([MySQLdb.escape_string(str(y).strip().replace("%","")) for yy,y in v.iteritems()])
            try:
                pp(dd)
                self.conn.ping(True)
                self.cursor = self.conn.cursor()
                self.cursor.executemany(sql,dd)
                self.conn.commit()
                del self.datas[:self.exesize]
            except Exception as ex:
                # import ipdb;ipdb.set_trace()
                self.conn.ping(True)
                print ex



if __name__ == '__main__':
    obj = main("city")
    obj()
